﻿using BW.WMS.Application.Features.Bins.Dtos;
using BW.WMS.Application.Features.Bins.Interfaces;
using BW.WMS.Core.Enums;
using BW.WMS.Core.Result;
using BW.WMS.Infrastructure.Data.Contexts;
using BW.WMS.Infrastructure.Services;
using Dapper;
using Microsoft.ML;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace BW.WMS.Application.Features.Bin.Services
{
    /// <summary>
    /// WMS仓库管理—货位服务实现层
    /// </summary>
    public class WMS_BinService : IWMS_BinService
    {
        private readonly DBContext dBContext;
        private readonly IRedisCacheService redisCacheService;
        private readonly Random _random = new Random();
        
        // 不同类型缓存的基础过期时间（分钟）
        private const int BinListCacheMinutes = 20;      // 货位列表缓存20分钟
        private const int BinDetailCacheMinutes = 35;    // 货位详情缓存35分钟
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dBContext">数据库上下文</param>
        /// <param name="redisCacheService">Redis缓存服务</param>
        public WMS_BinService(DBContext dBContext, IRedisCacheService redisCacheService)
        {
            this.dBContext = dBContext;
            this.redisCacheService = redisCacheService;
        }
        /// <summary>
        /// 获取货位分页列表，支持多条件查询
        /// </summary>
        /// <param name="queryDto">查询条件DTO</param>
        /// <returns>货位分页数据</returns>
        public async Task<ApiPage<WMS_BinDto>> GetBinPageListAsync(WMS_BinQueryDto queryDto)
        {
            var apiPage = new ApiPage<WMS_BinDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                PageData = new List<WMS_BinDto>()
            };

            try
            {
                // 生成缓存键
                var cacheKey = GenerateBinListCacheKey(queryDto);
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiPage<WMS_BinDto>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 计算分页偏移量
                int offset = (queryDto.PageIndex - 1) * queryDto.PageSize;

                // 构建查询条件
                var whereClause = new StringBuilder(" WHERE b.IsDeleted = 0");
                var parameters = new DynamicParameters();
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", queryDto.PageSize);

                if (!string.IsNullOrWhiteSpace(queryDto.Keyword))
                {
                    whereClause.Append(" AND (b.BinCode LIKE @Keyword OR b.BinName LIKE @Keyword)");
                    parameters.Add("@Keyword", $"%{queryDto.Keyword}%");
                }

                if (queryDto.Status.HasValue)
                {
                    whereClause.Append(" AND b.Status = @Status");
                    parameters.Add("@Status", queryDto.Status);
                }

                if (!string.IsNullOrWhiteSpace(queryDto.BinType))
                {
                    whereClause.Append(" AND b.BinType = @BinType");
                    parameters.Add("@BinType", queryDto.BinType);
                }

                if (queryDto.LocationId.HasValue)
                {
                    whereClause.Append(" AND b.LocationId = @LocationId");
                    parameters.Add("@LocationId", queryDto.LocationId);
                }

                if (queryDto.WarehouseId.HasValue)
                {
                    whereClause.Append(" AND l.WarehouseId = @WarehouseId");
                    parameters.Add("@WarehouseId", queryDto.WarehouseId);
                }

                // 计算总数
                var countSql = $@"
                    SELECT COUNT(1) 
                    FROM WMS_Bin b
                    LEFT JOIN WMS_Location l ON b.LocationId = l.Id AND l.IsDeleted = 0
                    {whereClause}";

                // 获取分页数据
                var pageSql = $@"
                    SELECT 
                        b.Id, b.BinCode, b.BinName, b.Length, b.Width, b.Height,
                        b.MaxWeight, b.UsedVolume, b.UsedWeight, b.Status, b.BinType,
                        b.SortOrder, b.Remark, b.LocationId, l.LocationName,
                        w.WarehouseName, b.CreateBy, b.CreateTime, b.UpdateBy, b.UpdateTime
                    FROM WMS_Bin b
                    LEFT JOIN WMS_Location l ON b.LocationId = l.Id AND l.IsDeleted = 0
                    LEFT JOIN WMS_Warehouse w ON l.WarehouseId = w.Id AND w.IsDeleted = 0
                    {whereClause}
                    ORDER BY b.SortOrder, b.CreateTime DESC
                    LIMIT @Offset, @PageSize";

                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 执行查询
                var bins = await connection.QueryAsync<WMS_BinDto>(pageSql, parameters);
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                // 转换枚举值为名称
                foreach (var bin in bins)
                {
                    bin.StatusName = Enum.GetName(typeof(BinStatusEnum), bin.Status);
                }

                // 设置返回数据
                apiPage.PageData = bins.ToList();
                apiPage.TodalCount = totalCount;

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(BinListCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, apiPage, expiration);
            }
            catch (Exception ex)
            {
                apiPage.Code = ApiEnums.Error;
                apiPage.Message = $"查询失败：{ex.Message}";
            }

            return apiPage;
        }

        /// <summary>
        /// 根据ID获取货位详情
        /// </summary>
        /// <param name="id">货位ID</param>
        /// <returns>货位详情DTO</returns>
        public async Task<ApiResult<WMS_BinDto>> GetBinByIdAsync(long id)
        {
            var result = new ApiResult<WMS_BinDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功"
            };

            try
            {
                // 生成缓存键
                var cacheKey = $"bin:detail:{id}";
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiResult<WMS_BinDto>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 构建查询货位信息的SQL
                var sql = @"
                    SELECT 
                        b.Id, b.BinCode, b.BinName, b.Length, b.Width, b.Height,
                        b.MaxWeight, b.UsedVolume, b.UsedWeight, b.Status, b.BinType,
                        b.SortOrder, b.Remark, b.LocationId, l.LocationName,
                        w.WarehouseName, b.CreateBy, b.CreateTime, b.UpdateBy, b.UpdateTime
                    FROM WMS_Bin b
                    LEFT JOIN WMS_Location l ON b.LocationId = l.Id AND l.IsDeleted = 0
                    LEFT JOIN WMS_Warehouse w ON l.WarehouseId = w.Id AND w.IsDeleted = 0
                    WHERE b.Id = @Id AND b.IsDeleted = 0";

                // 创建查询参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行查询，获取货位信息
                var bin = await connection.QueryFirstOrDefaultAsync<WMS_BinDto>(sql, parameters);

                // 如果货位不存在，返回失败信息
                if (bin == null)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货位不存在";
                    return result;
                }

                // 处理枚举值，转换为可读的状态名称
                bin.StatusName = Enum.GetName(typeof(BinStatusEnum), bin.Status);
                result.Result = bin;

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(BinDetailCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, result, expiration);
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"查询失败：{ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// 创建新货位
        /// </summary>
        /// <param name="createDto">货位创建DTO</param>
        /// <returns>操作结果，成功时返回新创建的货位ID</returns>
        public async Task<ApiResult<long>> CreateBinAsync(WMS_BinCreateDto createDto)
        {
            var result = new ApiResult<long>
            {
                Code = ApiEnums.Success,
                Message = "创建成功"
            };

            try
            {
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 检查货位编号是否已存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Bin WHERE BinCode = @BinCode AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@BinCode", createDto.BinCode);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货位编号已存在";
                    result.Result = 0;
                    return result;
                }

                // 检查库位是否存在
                var locationExistsSql = "SELECT COUNT(1) FROM WMS_Location WHERE Id = @LocationId AND IsDeleted = 0";
                var locationParams = new DynamicParameters();
                locationParams.Add("@LocationId", createDto.LocationId);

                var locationExists = await connection.ExecuteScalarAsync<int>(locationExistsSql, locationParams);

                if (locationExists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "所选库位不存在";
                    result.Result = 0;
                    return result;
                }

                // 生成雪花ID
                long id = YitIdHelper.NextId();

                // 构建插入货位数据的SQL
                var sql = @"
                    INSERT INTO WMS_Bin (
                        Id, BinCode, BinName, Length, Width, Height, MaxWeight,
                        UsedVolume, UsedWeight, Status, BinType, SortOrder, Remark,
                        LocationId, CreateBy, CreateTime, UpdateBy, UpdateTime, IsDeleted
                    ) VALUES (
                        @Id, @BinCode, @BinName, @Length, @Width, @Height, @MaxWeight,
                        @UsedVolume, @UsedWeight, @Status, @BinType, @SortOrder, @Remark,
                        @LocationId, @CreateBy, NOW(), @CreateBy, NOW(), 0
                    );";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@BinCode", createDto.BinCode);
                parameters.Add("@BinName", createDto.BinName);
                parameters.Add("@Length", createDto.Length);
                parameters.Add("@Width", createDto.Width);
                parameters.Add("@Height", createDto.Height);
                parameters.Add("@MaxWeight", createDto.MaxWeight);
                parameters.Add("@UsedVolume", createDto.UsedVolume);
                parameters.Add("@UsedWeight", createDto.UsedWeight);
                parameters.Add("@Status", createDto.Status);
                parameters.Add("@BinType", createDto.BinType);
                parameters.Add("@SortOrder", createDto.SortOrder);
                parameters.Add("@Remark", createDto.Remark);
                parameters.Add("@LocationId", createDto.LocationId);
                parameters.Add("@CreateBy", createDto.CreateBy);

                // 执行SQL
                await connection.ExecuteAsync(sql, parameters);
                result.Result = id;

                // 清理相关缓存
                await ClearBinCacheAsync();
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"创建失败：{ex.Message}";
                result.Result = 0;
            }

            return result;
        }

        /// <summary>
        /// 更新货位信息
        /// </summary>
        /// <param name="updateDto">货位更新DTO</param>
        /// <returns>操作结果，表示更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateBinAsync(WMS_BinUpdateDto updateDto)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 检查货位是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Bin WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", updateDto.Id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货位不存在";
                    result.Result = false;
                    return result;
                }

                // 构建更新货位数据的SQL
                var sql = @"
                    UPDATE WMS_Bin SET
                        BinName = @BinName,
                        Length = @Length,
                        Width = @Width,
                        Height = @Height,
                        MaxWeight = @MaxWeight,
                        UsedVolume = @UsedVolume,
                        UsedWeight = @UsedWeight,
                        Status = @Status,
                        BinType = @BinType,
                        SortOrder = @SortOrder,
                        Remark = @Remark,
                        UpdateBy = @UpdateBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", updateDto.Id);
                parameters.Add("@BinName", updateDto.BinName);
                parameters.Add("@Length", updateDto.Length);
                parameters.Add("@Width", updateDto.Width);
                parameters.Add("@Height", updateDto.Height);
                parameters.Add("@MaxWeight", updateDto.MaxWeight);
                parameters.Add("@UsedVolume", updateDto.UsedVolume);
                parameters.Add("@UsedWeight", updateDto.UsedWeight);
                parameters.Add("@Status", updateDto.Status);
                parameters.Add("@BinType", updateDto.BinType);
                parameters.Add("@SortOrder", updateDto.SortOrder);
                parameters.Add("@Remark", updateDto.Remark);
                parameters.Add("@UpdateBy", updateDto.UpdateBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearBinCacheAsync();
                }
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 删除货位（逻辑删除）
        /// </summary>
        /// <param name="id">货位ID</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> DeleteBinAsync(long id)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 检查货位是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Bin WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货位不存在";
                    result.Result = false;
                    return result;
                }

                // 构建逻辑删除货位的SQL
                var sql = @"
                    UPDATE WMS_Bin SET
                        IsDeleted = 1,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearBinCacheAsync();
                }
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 批量删除货位（逻辑删除）
        /// </summary>
        /// <param name="ids">货位ID列表</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> BatchDeleteBinsAsync(List<long> ids)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "批量删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 构建批量逻辑删除货位的SQL
                var sql = @"
                    UPDATE WMS_Bin SET
                        IsDeleted = 1,
                        UpdateTime = NOW()
                    WHERE Id IN @Ids";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Ids", ids);

                // 执行批量删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearBinCacheAsync();
                }
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"批量删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 更新货位状态
        /// </summary>
        /// <param name="id">货位ID</param>
        /// <param name="status">新状态值</param>
        /// <param name="updatedBy">操作人</param>
        /// <returns>操作结果，表示状态更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateBinStatusAsync(long id, int status, string updatedBy)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "状态更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dBContext.GetConn();

                // 检查货位是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Bin WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "货位不存在";
                    result.Result = false;
                    return result;
                }

                // 检查状态值是否在枚举范围内
                if (!Enum.IsDefined(typeof(BinStatusEnum), status))
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "无效的状态值";
                    result.Result = false;
                    return result;
                }

                // 构建更新货位状态的SQL
                var sql = @"
                    UPDATE WMS_Bin SET
                        Status = @Status,
                        UpdateBy = @UpdatedBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@Status", status);
                parameters.Add("@UpdatedBy", updatedBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearBinCacheAsync();
                }
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"状态更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 生成带随机因子的缓存过期时间，防止缓存雪崩
        /// </summary>
        /// <param name="baseMinutes">基础过期时间（分钟）</param>
        /// <param name="randomRangeMinutes">随机范围（分钟），默认为基础时间的20%</param>
        /// <returns>包含随机因子的过期时间</returns>
        private TimeSpan GetCacheExpiration(int baseMinutes, int? randomRangeMinutes = null)
        {
            var randomRange = randomRangeMinutes ?? (int)(baseMinutes * 0.2); // 默认20%的随机范围
            var randomOffset = _random.Next(-randomRange, randomRange + 1);
            var totalMinutes = Math.Max(1, baseMinutes + randomOffset); // 确保至少1分钟
            return TimeSpan.FromMinutes(totalMinutes);
        }

        /// <summary>
        /// 生成货位列表缓存键
        /// </summary>
        /// <param name="queryDto">查询条件</param>
        /// <returns>缓存键</returns>
        private string GenerateBinListCacheKey(WMS_BinQueryDto queryDto)
        {
            var keyBuilder = new StringBuilder("bin:list:");
            keyBuilder.Append($"page:{queryDto.PageIndex}:");
            keyBuilder.Append($"size:{queryDto.PageSize}:");
            
            if (!string.IsNullOrWhiteSpace(queryDto.Keyword))
                keyBuilder.Append($"keyword:{queryDto.Keyword}:");
            
            if (queryDto.Status.HasValue)
                keyBuilder.Append($"status:{queryDto.Status}:");
                
            if (!string.IsNullOrWhiteSpace(queryDto.BinType))
                keyBuilder.Append($"type:{queryDto.BinType}:");
                
            if (queryDto.LocationId.HasValue)
                keyBuilder.Append($"location:{queryDto.LocationId}:");
                
            if (queryDto.WarehouseId.HasValue)
                keyBuilder.Append($"warehouse:{queryDto.WarehouseId}:");

            return keyBuilder.ToString();
        }

        /// <summary>
        /// 清理货位相关缓存
        /// </summary>
        /// <returns></returns>
        private async Task ClearBinCacheAsync()
        {
            var patterns = new[]
            {
                "bin:list:*",
                "bin:detail:*"
            };

            foreach (var pattern in patterns)
            {
                // 注意：Redis集群模式不支持通配符删除，这里采用简化处理
                // 在实际生产环境中，可以考虑使用Redis Scan命令或者设置较短的过期时间
                await redisCacheService.DeleteAsync(pattern);
            }
        }
    }
}
